﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;

namespace CodeAngel.Domain.Framework
{

    #region IFilter

    /// <summary>
    /// An interface for an object that can be used to identifiy a subset of entities for a 
    /// particular entity
    /// </summary>
    public interface IFilter
    {

        /// <summary>
        /// To create the SQL string representation of this filter.
        /// </summary>
        string ToSqlString();

    }

    #endregion

    #region Filter

    /// <summary>
    /// Base class for all stronlgy-typed filter classes.  Each entity that supports filtering
    /// should have its own filter class derived from this class.
    /// </summary>
    /// <remarks>
    /// A filter object is used to identifiy a subset of entities for a particular entity.  This is
    /// accomplised through this base class and derived classes through a very simple SELECT clause
    /// that identifies the primary key for our target entity, a collection of table joins that
    /// identify how one table relates to another (this enables specifiying criteria from child 
    /// entities, and a collection of zero or more criterion objects, which identify the conditions
    /// that should be met for an entity to be included when running this filter.
    /// </remarks>
    [EditorBrowsable(EditorBrowsableState.Never)]
    public abstract class Filter : IFilter
    {

        #region Constructors

        protected Filter(string tableName, string primaryKey)
        {
            this.TableName = tableName;
            this.PrimaryKey = primaryKey;
            this.TableJoins = new List<TableJoin>();
            this.DefaultConnectorOperator = ConnectorOperator.AND;
        }

        #endregion

        #region TableName and PrimaryKey

        /// <summary>
        /// Identifies the name of the table that this filter applies to.
        /// </summary>
        public string TableName { get; private set; }

        /// <summary>
        /// Identifies the primary key of the table that this filter applies to.
        /// </summary>
        public string PrimaryKey { get; private set; }

        #endregion

        #region Description

        /// <summary>
        /// Gets or sets a user friendly description of the current filter and its criteria. This
        /// is not used in the actual filter operation.
        /// </summary>
        public string Description { get; set; }

        #endregion

        #region TableJoins

        /// <summary>
        /// Identifies how the tables in this filter are related to one another.
        /// </summary>
        protected List<TableJoin> TableJoins { get; private set; }

        /// <summary>
        /// Adds a new table join using the identified tables and keys.
        /// </summary>
        protected TableJoin AddTableJoin(string parentTable, string parentTableForeignKey, string childTable, string childTablePrimaryKey)
        {
            TableJoin ThisTableJoin = new TableJoin(parentTable, parentTableForeignKey, childTable, childTablePrimaryKey);
            this.TableJoins.Add(ThisTableJoin);
            return ThisTableJoin;
        }

        #endregion

        #region DefaultConnectorOperator

        /// <summary>
        /// Gets or sets the connector operator to use when a criteria object is added and 
        /// the connector operator is not identified.
        /// </summary>
        public ConnectorOperator DefaultConnectorOperator { get; set; }

        #endregion

        #region ToSqlString

        /// <summary>
        /// To create the SQL string representation of this filter.
        /// </summary>
        public string ToSqlString()
        {
            //return string.Format("{0}{1}{2}", this.GetSelectClause(), this.GetJoinClause(), this.GetWhereClause());
            return this.GetSelectClause() + this.GetJoinClause() + this.GetWhereClause();
        }

        /// <summary>
        /// Returns the appropriate SQL select clause for this filter.
        /// </summary>
        private string GetSelectClause()
        {
            return string.Format("SELECT DISTINCT [{0}].[{1}] FROM [{0}]", this.TableName, this.PrimaryKey);
        }

        /// <summary>
        /// Returns the appropriate SQL join clause(s) for this filter (based on our TableJoins).
        /// </summary>
        private string GetJoinClause()
        {
            string JoinClauses = "";
            foreach (var ThisTableJoin in this.TableJoins)
                JoinClauses += " " + ThisTableJoin.ToSqlJoinClause();
            return JoinClauses;
        }

        /// <summary>
        /// Returns the SQL WHERE clause for this filter (based on the criteria added to this object).
        /// </summary>
        private string GetWhereClause()
        {
            if (this._CriteriaBuilder.Length > 0)
                return " WHERE (" + this._CriteriaBuilder.ToString() + ")";
            else
                return " WHERE (1 = 1)";
        }

        #endregion

        #region Add

        private bool _OnFirstCriteria = true;
        private bool _OnFirstGroup = true;
        private bool _NewGroupStarted = true;
        private bool _GroupConnectorHasChanged = false;
        private bool _ShouldStartNewGroup = true;
        private ConnectorOperator _CurrentGroupConnector;
        private StringBuilder _CriteriaBuilder = new StringBuilder();

        /// <summary>
        /// To add the identified criterion.
        /// </summary>
        protected void Add(ICriterion thisCriterion)
        {
            if (thisCriterion.IsSet) this.Add(thisCriterion, this.DefaultConnectorOperator, false);
        }


        /// <summary>
        /// To add the identified criterion.
        /// </summary>
        /// <param name="thisCriterion">The criterion to add.</param>
        /// <param name="connector">How this criterion relates to the most recently added criterion/grouping.</param>
        /// <param name="beginNewGroup">Begins a new group, starting with this criterion.</param>
        protected void Add(ICriterion thisCriterion, ConnectorOperator connector, bool beginNewGroup)
        {

            // There is nothing to do if the identified criteria has not been set.
            if (!thisCriterion.IsSet) return;


            //this.mCriteria.Add(thisCriteria);

            // If we have started a new group, then keep track what the connector is for this group,
            // which will always be the connector of the second Criteria added to the group.
            if (this._NewGroupStarted) this._CurrentGroupConnector = connector;

            //Determine if the connector operator for the current group has changed.
            this._GroupConnectorHasChanged = this._CurrentGroupConnector != connector;

            //Determine if we need to start a new group.
            this._ShouldStartNewGroup = (beginNewGroup || this._GroupConnectorHasChanged) && !this._OnFirstCriteria;
            this._NewGroupStarted = this._ShouldStartNewGroup | this._OnFirstCriteria;
            if (this._ShouldStartNewGroup)
            {

                //Wrap the existing group in their own parenthese.
                this._CriteriaBuilder.Insert(0, "(");
                this._CriteriaBuilder.Append(")");

                //Since we have started a new group, we will never again be on the first group.
                this._OnFirstGroup = false;
            }
            else
            {

                //Since we are not starting a new group, but merely adding another Criteria to the
                //current
                //group, we will need to remove the trailing parenthesis.
                if (!this._OnFirstGroup) this._CriteriaBuilder.Remove(this._CriteriaBuilder.Length - 1, 1);
            }

            //If we are not on the first Criteria, we'll need to add the connector operator.
            if (!this._OnFirstCriteria) this._CriteriaBuilder.Append(GetConnectorOperatorAsSQLString(connector));

            if (this._ShouldStartNewGroup) this._CriteriaBuilder.Append("(");
            this._CriteriaBuilder.Append(thisCriterion.ToSqlString());
            if (!this._OnFirstGroup) this._CriteriaBuilder.Append(")");

            this._OnFirstCriteria = false;
        }

        #endregion

        #region Clear

        /// <summary>
        /// Clears any criteria from this filter.
        /// </summary>
        public void Clear()
        {
            this._CriteriaBuilder = new StringBuilder();
        }

        #endregion

        #region GetConnectorOperatorAsSQLString

        /// <summary>
        /// To translate the connector operator enum to its SQL string equivalent.
        /// </summary>
        static internal string GetConnectorOperatorAsSQLString(ConnectorOperator value)
        {
            return value == ConnectorOperator.AND ? " AND " : " OR ";
        }

        #endregion

    }

    #endregion

    #region TableJoin

    /// <summary>
    /// Identifies how two tables should be joined together within a Filter object.
    /// </summary>
    public class TableJoin
    {
        public TableJoin(string parentTable, string parentTableForeignKey, string childTable, string childTablePrimaryKey)
        {
            this.ParentTable = parentTable;
            this.ParentTableForeignKey = parentTableForeignKey;
            this.ChildTable = childTable;
            this.ChildTablePrimarykey = childTablePrimaryKey;
        }

        public string ParentTable { get; private set; }
        public string ParentTableForeignKey { get; private set; }

        public string ChildTable { get; private set; }
        public string ChildTablePrimarykey { get; private set; }

        /// <summary>
        /// Translates this table join to the appropriate SQL join clause syntax.
        /// </summary>
        public string ToSqlJoinClause()
        {
            return string.Format("LEFT OUTER JOIN [{0}] ON [{0}].[{1}] = [{2}].[{3}]", this.ChildTable, this.ChildTablePrimarykey, this.ParentTable, this.ParentTableForeignKey);
        }

    }

    #endregion

    #region ConnectorOperator

    /// <summary>
    /// Identifies how two criterions are connected to one another.
    /// </summary>
    public enum ConnectorOperator { AND, OR }

    #endregion

    #region IComparisonOperator

    /// <summary>
    /// Defines a contract for a comparison operator. A comparison operator is used to establish a
    /// relationship between a column and one or more values which when used in the WHERE clause
    /// of a SQL statement, identifies which rows should be affected.
    /// </summary>
    public interface IComparisonOperator
    {

        /// <summary>
        /// Identifies the string representation of this comparison operator appropriate to
        /// use within the WHERE clause of a SQL SELECT statement.
        /// </summary>
        string Value { get; }

        /// <summary>
        /// Identifies the name of this comparison operator in a format appropriate to display to 
        /// an end user.
        /// </summary>
        string FriendlyName { get; }

    }

    #endregion

    #region ComparisonOperator

    /// <summary>
    /// Defines an object that establishes a relationship between a specific column in a table and
    /// one or more values, which, when used in the WHERE clause of a SQL SELECT statement, identifies 
    /// which rows should be affected.
    /// </summary>
    public class ComparisonOperator : IComparisonOperator
    {

        internal ComparisonOperator(string value, string friendlyName)
        {
            this.Value = value;
            this.FriendlyName = friendlyName;
        }

        /// <summary>
        /// Identifies the string representation of this comparison operator appropriate to
        /// use within the WHERE clause of a SQL SELECT statement.
        /// </summary>
        public string Value { get; private set; }

        /// <summary>
        /// Identifies the name of this comparison operator in a format appropriate to display to 
        /// an end user.
        /// </summary>
        public string FriendlyName { get; private set; }

        /// <summary>
        /// Identifies a simple string representation of this object.
        /// </summary>
        public override string ToString()
        {
            return this.FriendlyName;
        }

    }

    #endregion

    #region ComparisonOperators
    
    /// <summary>
    /// Identifies the set of comparison operators supported by this framework.
    /// </summary>
    public static class ComparisonOperators
    {

        public static ComparisonOperator IS = new ComparisonOperator("IS", "Is");
        public static ComparisonOperator IsNot = new ComparisonOperator("IS NOT", "Is Not");
        public static ComparisonOperator IN = new ComparisonOperator("IN", "In");
        public static ComparisonOperator Like = new ComparisonOperator("LIKE", "Similar To");

        //Numeric Comparison Operators
        public static ComparisonOperator EqualTo = new ComparisonOperator("=", " = ");
        public static ComparisonOperator NotEqualTo = new ComparisonOperator("<>", " <> ");
        public static ComparisonOperator GreaterThan = new ComparisonOperator(">", " > ");
        public static ComparisonOperator GreaterThanOrEqualTo = new ComparisonOperator(">=", " >= ");
        public static ComparisonOperator LessThan = new ComparisonOperator("<", " < ");
        public static ComparisonOperator LessThanOrEqualTo = new ComparisonOperator("<=", " <= ");

        //String Comparison Operators
        public static ComparisonOperator BeginsWith = new ComparisonOperator("LIKE", "Begins With");
        public static ComparisonOperator Contains = new ComparisonOperator("LIKE", "Contains");
        public static ComparisonOperator EndsWith = new ComparisonOperator("LIKE", "Ends With");
        public static ComparisonOperator IsExactly = new ComparisonOperator("=", "Is (exactly)");

        //Date Comparison Operators
        public static ComparisonOperator On = new ComparisonOperator("=", "On");
        public static ComparisonOperator After = new ComparisonOperator(">", "After");
        public static ComparisonOperator OnOrAfter = new ComparisonOperator(">=", "On or After");
        public static ComparisonOperator Before = new ComparisonOperator("<", "Before");
        public static ComparisonOperator OnOrBefore = new ComparisonOperator("<=", "On or Before");
        public static ComparisonOperator Between = new ComparisonOperator("BETWEEN", "Between");

    }

    #endregion

    #region ICriterion

    /// <summary>
    /// A contract for any object that can serve as a specific criterion in a SQL Where clause.
    /// </summary>
    /// <remarks>
    /// Consider the following SQL SELECT statement:
    ///		SELECT * FROM Employee WHERE Employee.LastName LIKE 'S%' AND Employee.HireDate BETWEEN '1/15/2003' AND '1/14/2004'
    ///	One ICriterion object would represent "Employee.LastName LIKE 'S%'" and another 
    ///	"Employee.HireDate BETWEEN '1/15/2003' AND '1/14/2004'".  Thus an ICriterion object identifies 
    ///	four key elements: a table name, a column name, a comparison operator, and a value
    ///	(where a value can represent a single value or mulitple values).
    /// </remarks>
    public interface ICriterion
    {

        /// <summary>
        /// Identifies the name of the table of which our ColumnName is a member of.
        /// </summary>
        string TableName { get; }

        /// <summary>
        /// Identifies the name of the column.
        /// </summary>
        string ColumnName { get; }

        /// <summary>
        /// Identifies the name of this crtierion in a format appropriate to display to an end user.
        /// This property is not used in any SQL statement.
        /// </summary>
        string FriendlyName { get; }

        /// <summary>
        /// Identifies how the values of our column (in our table) should be compared to the Value of 
        /// this criterion to determine which rows should be affected when used within a SQL WHERE clause. 
        /// </summary>
        IComparisonOperator ComparisonOperator { get; set; }

        /// <summary>
        /// Identifies if this criterion can currently be used as is (i.e. both the comparision
        /// operator and value properties have been identified).
        /// </summary>
        /// <remarks>
        /// If this criterion is not set, then it should not be used in any filter operation.
        /// </remarks>
        bool IsSet { get; }

        /// <summary>
        /// Clears all settings of this criterion.
        /// </summary>
        /// <remarks>
        /// If this criterion had been added to a Filter object, after calling this method it will
        /// still be a part of that filter.  But unless both its Value and ComparisonOpertor
        /// properties are set, this criterion should not participate in the Where clause of the SQL
        /// filter.
        /// </remarks>
        void Clear();

        /// <summary>
        /// Identifies this criterion as a string suitable for the participation in the Where
        /// clause of a SQL statement.
        /// </summary>
        string ToSqlString();

    }

    #endregion

    #region Criterion

    /// <summary>
    /// A base class for strongly-typed classes that can serve as a specific criterion in a SQL Where clause.
    /// </summary>
    /// <remarks>
    /// Consider the following SQL SELECT statement:
    ///		SELECT * FROM Employee WHERE Employee.LastName LIKE 'S%' AND Employee.HireDate BETWEEN '1/15/2003' AND '1/14/2004'
    ///	One criterion object would represent "Employee.LastName LIKE 'S%'" and another 
    ///	"Employee.HireDate BETWEEN '1/15/2003' AND '1/14/2004'".  A criterion object identifies 
    ///	four key elements: a table name, a column name, a comparison operator, and a value
    ///	(where a value can represent a single value or mulitple values).
    /// </remarks>
    [EditorBrowsable(EditorBrowsableState.Never)]
    public abstract class Criterion<T> : ICriterion
    {

        #region Constructors

        protected Criterion(string tableName, string columnName, string friendlyName)
        {
            this.TableName = tableName;
            this.ColumnName = columnName;
            this.FriendlyName = friendlyName;
        }

        #endregion

        #region TableName

        /// <summary>
        /// Identifies the name of the table of which our ColumnName is a member of.
        /// </summary>
        public string TableName { get; private set; }

        #endregion

        #region ColumnName

        /// <summary>
        /// Identifies the name of the column.
        /// </summary>
        public string ColumnName { get; private set; }

        #endregion

        #region FriendlyName

        /// <summary>
        /// Identifies the name of this crtierion in a format appropriate to display to an end user.
        /// This property is not used in any SQL statement.
        /// </summary>
        public string FriendlyName { get; private set; }

        #endregion

        #region ComparisonOperator

        /// <summary>
        /// Identifies how the values of our column (in our table) should be compared to the Value of 
        /// this criterion to determine which rows should be affected when used within a SQL WHERE clause. 
        /// </summary>
        public IComparisonOperator ComparisonOperator { get; set; }

        #endregion

        #region Set Value(s)

        /// <summary>
        /// Sets the value of this criterion to a single value.
        /// </summary>
        /// <remarks>
        /// Use this to create a criterion such as:  Employee.FirstName = 'John' or Employee.Age >= 21
        /// </remarks>
        public virtual void SetValue(T value)
        {
            this._Value = value;
            this._ValueSetting = ValueSetting.Single;
        }
        protected T _Value;

        /// <summary>
        /// Sets the value of this criterion to a start and end value, and defaults the comparison 
        /// operator to Between - which is the only valid operator when using a range.
        /// </summary>
        /// <remarks>
        /// Use this to create a criterion such as:  Employee.HireDate BETWEEN '1/15/2003' AND '1/14/2004'
        /// </remarks>
        public virtual void SetRange(T startValue, T endValue)
        {
            this._StartValue = startValue;
            this._EndValue = endValue;
            this._ValueSetting = ValueSetting.Range;
            this.ComparisonOperator = ComparisonOperators.Between;
        }
        protected T _StartValue;
        protected T _EndValue;

        /// <summary>
        /// Sets the value of this criterion to a set of values.
        /// </summary>
        /// <remarks>
        /// Use this to create a criterion such as:  Employee.PositionId IN (12, 15, 19, 63)
        /// </remarks>
        public virtual void SetValues(IEnumerable<T> values)
        {
            this._Values = values;
            this._ValueSetting = ValueSetting.Collection;
        }
        protected IEnumerable<T> _Values;

        /// <summary>
        /// Sets the value of this criterion to NULL.
        /// </summary>
        /// <remarks>
        /// Use this to create a criterion such as:  Employee.PositionId IS NULL or Employee.PositionId IS NOT NULL
        /// </remarks>
        public void SetValueToNull()
        {
            this._ValueSetting = ValueSetting.Null;
        }

        /// <summary>
        /// Identifies how our value is set.
        /// </summary>
        private ValueSetting _ValueSetting = ValueSetting.NotSet;
        private enum ValueSetting { NotSet, Single, Collection, Range, Null }

        #endregion

        #region IsSet

        /// <summary>
        /// Identifies if this criterion object can currently be used as is (i.e. both comparision
        /// operator and value(s) have been identified).
        /// </summary>
        /// <remarks>
        /// If this criterion is not set, then it should not be part of the filter's WHERE clause.
        /// </remarks>
        public bool IsSet
        {
            get
            {
                return (this._ValueSetting != ValueSetting.NotSet) && (this.ComparisonOperator != null);
            }
        }

        #endregion

        #region Clear

        /// <summary>
        /// To clear all adjustable settings of this criterion.
        /// </summary>
        public void Clear()
        {
            this.ComparisonOperator = null;
            this._ValueSetting = ValueSetting.NotSet;
        }

        #endregion

        #region ToSqlString

        /// <summary>
        /// Creates and returns a string that represents this criterion suitable to use in the WHERE 
        /// clause a SQL statement.
        /// </summary>
        public string ToSqlString()
        {
            switch (this._ValueSetting)
            {
                case ValueSetting.NotSet:
                    return string.Empty;

                case ValueSetting.Single:
                    if (this.ComparisonOperator == ComparisonOperators.IN || this.ComparisonOperator == ComparisonOperators.Between)
                        throw new InvalidComparisonOperatorException("Cannot use ComparisonOperators.IN or ComparisonOperators.Between operator with a single value criterion.", this);
                    return this.GetQualifiedColumn() + " " + this.ComparisonOperator.Value + " " + this.FormatValue(this._Value);

                case ValueSetting.Collection:
                    if (this.ComparisonOperator != ComparisonOperators.IN)
                        throw new InvalidComparisonOperatorException("Cannot use a collection of values without using the ComparisonOperators.IN operator.", this);
                    return this.GetQualifiedColumn() + " " + this.ComparisonOperator.Value + " " + this.GetValuesAsDelimitedString();

                case ValueSetting.Range:
                    if (this.ComparisonOperator != ComparisonOperators.Between)
                        throw new InvalidComparisonOperatorException("Cannot use a range of values without using the ComparisonOperators.Between operator.", this);
                    return this.GetQualifiedColumn() + " " + this.ComparisonOperator.Value + " " + this.FormatValue(this._StartValue) + " AND " + this.FormatValue(this._EndValue);

                case ValueSetting.Null:
                    if (this.ComparisonOperator != ComparisonOperators.IS && this.ComparisonOperator != ComparisonOperators.IsNot)
                        throw new InvalidComparisonOperatorException("Cannot use a NULL value without using the ComparisonOperators.IS or ComparisonOperators.IsNot operator.", this);
                    return this.GetQualifiedColumn() + " " + this.ComparisonOperator.Value + " NULL";

                default:
                    throw new Exception("ToSqlString cannot be evaluated.  Unknown value setting.  Table.Column Name: " + this.TableName + "." + this.ColumnName);

            }

        }

        #endregion

        #region ToString

        /// <summary>
        /// Identifies the friendly name of this criterion.
        /// </summary>
        public override string ToString()
        {
            return this.FriendlyName;
        }

        #endregion

        #region Support Procedures

        /// <summary>
        /// Gets the fully qualified name of our column.
        /// </summary>
        private string GetQualifiedColumn()
        {
            return string.Format("[{0}].[{1}]", this.TableName, this.ColumnName);
        }

        /// <summary>
        /// Returns our _Values collection as a list of comma delimited strings suitable to use in 
        /// a SQL WHERE clause.
        /// </summary>
        private string GetValuesAsDelimitedString()
        {
            if (this._Values == null) throw new Exception("Cannot execute CodeAngel.Domain.Filter.Criterion<T>.GetValuesAsDelimitedString() - _Values is null.");

            StringBuilder ThisSB = new StringBuilder();
            ThisSB.Append("(");
            foreach (T ThisValue in this._Values)
                ThisSB.Append(this.FormatValue(ThisValue) + ", ");

            // Remove the trailing ", " value connector, close off the collection and we're done.
            ThisSB.Remove(ThisSB.Length - 2, 2);
            ThisSB.Append(")");

            return ThisSB.ToString();
        }

        /// <summary>
        /// To format the identified value to a SQL recognizable string based on its data type.
        /// </summary>
        private string FormatValue(object value)
        {
            switch (value.GetType().Name)
            {
                case "Guid":
                    return "'" + value.ToString() + "'";

                case "Int16":
                case "Int32":
                case "Int64":
                case "Single":
                case "Double":
                case "Decimal":
                    return value.ToString();

                case "Boolean":
                    return ((bool)value ? "1" : "0");

                case "DateTime":
                    return "'" + ((DateTime)value).ToString("d") + "'";

                default:
                    string ValueAsString = value.ToString();                    
                    ValueAsString = ValueAsString.Replace("'", "''"); // Tag any single quotes with another single quote - to make SQL Server happy.
                    return "'" + this.AppendWildCardCharactersIfAppropriate(ValueAsString) + "'";

            }
        }

        /// <summary>
        /// Identifies the wild card character used by SQL Server to perform 'LIKE' comparisons.
        /// </summary>
        private const string WILD_CARD_CHARACTER = "%";

        /// <summary>
        /// Adds a wild card character to the left and/or right of the identified value if appropriate 
        /// (based on the current ComparisonOperator).
        /// </summary>
        private string AppendWildCardCharactersIfAppropriate(string value)
        {
            if (this.ComparisonOperator == ComparisonOperators.Contains)
            {
                string ReturnValue = null;
                ReturnValue = this.AddWildCardToLeftIfMissing(value);
                ReturnValue = this.AddWildCardToRightIfMissing(ReturnValue);
                return ReturnValue;
            }

            else if (this.ComparisonOperator == ComparisonOperators.BeginsWith)
                return this.AddWildCardToRightIfMissing(value);

            else if (this.ComparisonOperator == ComparisonOperators.EndsWith)
                return this.AddWildCardToLeftIfMissing(value);

            else
                return value;
        }

        /// <summary>
        /// Adds a wild card character to the left of the identified value if it is missing.
        /// </summary>
        private string AddWildCardToLeftIfMissing(string value)
        {
            if (!value.StartsWith(WILD_CARD_CHARACTER)) value = WILD_CARD_CHARACTER + value;
            return value;
        }

        /// <summary>
        /// Adds a wild card character to the right of the identified value if it is missing.
        /// </summary>
        private string AddWildCardToRightIfMissing(string value)
        {
            if (!value.EndsWith(WILD_CARD_CHARACTER)) value += WILD_CARD_CHARACTER;
            return value;
        }

        #endregion

    }

    #endregion

    #region InvalidComparisonOperatorException

    /// <summary>
    /// Identifies that the comparison operator is not valud for the value(s) set on an ICriterion object.
    /// </summary>
    /// <example>
    /// var ThisFirstNameCriterion = new FirstNameCriterion();
    /// ThisFirstNameCriterion.SetValue("John");
    /// ThisFirstNameCriterion.ComparisonOperator = ComparisonOperators.BETWEEN;
    /// ThisFirstNameCriterion.ToSqlString();
    /// This will result in an InvalidComparisonOperatorException exception, since this would be an invalid 
    /// condition in a SQL WHERE clause:  Employee.FirstName BETWEEN 'John'
    /// </example>
    public class InvalidComparisonOperatorException : Exception
    {
        public InvalidComparisonOperatorException(string message, ICriterion criterion)
            : base(message)
        {
            this.Criterion = criterion;
        }

        public ICriterion Criterion { get; private set; }
    }

    #endregion

    //#region StringCriterion

    ///// <summary>
    ///// A criterion class that works only with String data types.
    ///// </summary>
    ///// <remarks>
    ///// Since single quotes hold a special role in SQL syntax, any single quote used within a
    ///// search value will be escaped with another single quote.
    ///// </remarks>
    //[EditorBrowsable(EditorBrowsableState.Never)]
    //public abstract class StringCriterion : Criterion<string>
    //{

    //    protected StringCriterion(string tableName, string columnName, string friendlyName)
    //        : base(tableName, columnName, friendlyName) { }

    //    /// <summary>
    //    /// Sets the value of this criterion to a single value.
    //    /// </summary>
    //    public override void SetValue(string value)
    //    {
    //        // Tag any single quotes with another single quote - to make SQL Server happy.
    //        value = value.Replace("'", "''");
    //        base.SetValue(value);
    //    }

    //    /// <summary>
    //    /// Sets the value of this criterion to a start and end value, and defaults the comparison 
    //    /// operator to Between - which is the only valid operator when using a range.
    //    /// </summary>
    //    public override void SetRange(string startValue, string endValue)
    //    {
    //        // Tag any single quotes with another single quote - to make SQL Server happy.
    //        startValue = startValue.Replace("'", "''");
    //        endValue = endValue.Replace("'", "''");
    //        base.SetRange(startValue, endValue);
    //    }

    //    /// <summary>
    //    /// Sets the value of this criterion to a set of values.
    //    /// </summary>
    //    public override void SetValues(IEnumerable<string> values)
    //    {
    //        // Tag any single quotes with another single quote - to make SQL Server happy.
    //        List<string> FormattedValues = new List<string>();
    //        foreach (string ThisString in values)
    //            FormattedValues.Add(ThisString.Replace("'", "''"));
    //        base.SetValues(FormattedValues);
    //    }

    //}

    //#endregion

}
